/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package me.zhengjie.business.repository;

import me.zhengjie.business.domain.Jzdj;
import me.zhengjie.business.domain.vo.PersonFeeVo;
import me.zhengjie.business.domain.vo.ZyryVo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
* @website https://el-admin.vip
* @author xiahongbo
* @date 2022-06-06
**/
public interface JzdjRepository extends JpaRepository<Jzdj, Long>, JpaSpecificationExecutor<Jzdj> {

    /**
     * 根据身份证查询入院的人员
     * @param sfz /
     * @return /
     */
    @Query(value = "select t.* from BIS_JZDJ t where t.bis_jzdj_sfz=?1 and t.bis_jzdj_jylx='1'",nativeQuery = true)
    Jzdj findJzjl(String sfz);

    /**
     * 根据身份证和日期查询，是否存在在院信息
     * @param sfz /
     * @return /
     */
    @Query(value = "select t.* from BIS_JZDJ t  where 1=1 " +
            "and t.bis_jzdj_sfz = ?1 " +
            "and t.bis_jzdj_ryrq <= ?2 and t.bis_jzdj_cyrq >= ?2 " +
            "and t.bis_jzdj_ryjbbm='ZH00001' " +
            "and t.bis_jzdj_jylx!='-1' and rownum=1 ",nativeQuery = true)
    Jzdj isZy(String sfz, String ryrq);


    @Query(
            value = "SELECT " +
                    "R2.BIS_JZDJ_ID, " +
                    "R2.BIS_JZDJ_LSH, " +
                    "R2.BIS_JZDJ_YLLB, " +
                    "to_char(to_date(R2.BIS_JZDJ_RYRQ, 'yyyy-mm-dd'), 'yyyy-mm-dd') BIS_JZDJ_RYRQ, " +
                    "R2.BIS_JZDJ_JBR, " +
                    "to_char(to_date(R2.BIS_JZDJ_JBRQ, 'yyyy-mm-dd'), 'yyyy-mm-dd') BIS_JZDJ_JBRQ, " +
                    "R2.BIS_JZDJ_FPH, " +
                    "R2.BIS_JZDJ_KSMC, " +
                    "R2.BIS_JZDJ_CWH, " +
                    "R2.BIS_JZDJ_YSXM, " +
                    "R2.BIS_JZDJ_HZLXFS, " +
                    "R2.BIS_JZDJ_JSLXFS, " +
                    "R2.BIS_JZDJ_JYLX, " +
                    "R1.BIS_ZHSQ_SNRY_XM, " +
                    "R1.BIS_ZHSQ_SNRY_XB, " +
                    "R1.BIS_ZHSQ_SNRY_SFZ, " +
                    "R1.BIS_ZHSQ_GRBH, " +
                    "R1.BIS_ZHSQ_YBKH, " +
                    "R1.BIS_ZHSQ_SNDJ, " +
                    "R1.BIS_ZHSQ_SNRY_YBSSQX, " +
                    "to_char(R1.BIS_ZHSQ_DYKKRQ, 'yyyy-mm-dd'), " +
                    "to_char(R1.BIS_ZHSQ_DYJSRQ, 'yyyy-mm-dd'), " +
                    "R1.BIS_ZHSQ_XSDYQ, " +
                    "R2.BIS_JZDJ_BS, " +
                    "R1.BIS_ZHSQ_ID " +
                    "FROM BIS_ZHSQ R1, BIS_JZDJ R2 " +
                    "WHERE R1.BIS_ZHSQ_ID = R2.BIS_JZDJ_ZHSQ_ID " +
                    "AND R2.BIS_JZDJ_JYLX='1' " +
                    "AND R2.BIS_JZDJ_CYZT='0' " +
                    "AND (R1.BIS_ZHSQ_SNRY_XM like '%'||?1||'%' or R1.BIS_ZHSQ_SNRY_SFZ like '%'||?1||'%'or R1.BIS_ZHSQ_JHRDH like '%'||?1||'%' ) ",
            nativeQuery = true)
    List<Object> queryList1(String blurry, Pageable pageable);


    @Query(value = "select new me.zhengjie.business.domain.vo.ZyryVo(" +
            "R2.bisJzdjId, " +
            "R2.bisJzdjLsh, " +
            "R2.bisJzdjYllb, " +
            "R2.bisJzdjRyrq, " +
            "R2.bisJzdjJbr, " +
            "R2.bisJzdjJbrq, " +
            "R2.bisJzdjFph, " +
            "R2.bisJzdjKsmc, " +
            "R2.bisJzdjBs, " +
            "R2.bisJzdjCwh, " +
            "R2.bisJzdjYsxm, " +
            "R2.bisJzdjHzlxfs, " +
            "R2.bisJzdjJslxfs, " +
            "R2.bisJzdjJylx, " +
            "R1.bisZhsqId, " +
            "R1.bisZhsqSnryXm, " +
            "R1.bisZhsqSnryXb, " +
            "R1.bisZhsqSnrySfz, " +
            "R1.bisZhsqGrbh, " +
            "R1.bisZhsqYbkh, " +
            "R1.bisZhsqSndj, " +
            "R1.bisZhsqSnryYbssqx, " +
            "R1.bisZhsqDykkrq, " +
            "R1.bisZhsqDyjsrq, " +
            "R1.bisZhsqXsdyq," +
            "R2.lrsj)" +
            "FROM Zhsq R1, Jzdj R2 " +
            "WHERE R1.bisZhsqId = R2.bisJzdjZhsqId " +
            "AND R2.bisJzdjJylx='1' " +
            "AND R2.bisJzdjCyzt='0' " +
            "AND R1.bisZhsqZhlb='01' "+
            "AND R1.bisZhsqHljgBm=?1 "+
            "AND (?2 IS NULL OR ?2 = '' OR R1.bisZhsqSnrySfz like '%'||?2||'%' OR R1.bisZhsqSnryXm like '%'||?2||'%' ) ",
            countQuery = "select count(*) " +
                    "FROM Zhsq R1, Jzdj R2 " +
                    "WHERE R1.bisZhsqId = R2.bisJzdjZhsqId " +
                    "AND R2.bisJzdjJylx='1' " +
                    "AND R2.bisJzdjCyzt='0' " +
                    "AND R1.bisZhsqZhlb='01' "+
                    "AND R1.bisZhsqHljgBm=?1 "+
                    "AND (?2 IS NULL OR ?2 = '' OR R1.bisZhsqSnrySfz like '%'||?2||'%' OR R1.bisZhsqSnryXm like '%'||?2||'%' ) ")
    Page<ZyryVo> queryList(String yljgdm, String blurry, Pageable pageable);

    @Query(value = "select new me.zhengjie.business.domain.vo.PersonFeeVo(" +
            "R1.aac001, " +
            "R1.aac003, " +
            "R1.aac002, " +
            "R1.accrymBegn, " +
            "R1.accrymEnd, " +
            "R1.insutype, " +
            "R1.clctClag)" +
            "FROM PersonFee R1 " +
            "WHERE 1=1 " +
            "and ((R1.insutype='390' and accrym_begn >=?2 and accrym_end >=?3 ) or (R1.insutype='411' and accrym_begn >=?2 and accrym_end >=?3 ))" +
            "and (?1 IS NULL OR ?1 = '' OR R1.aac001 like '%'||?1||'%' OR R1.aac002 like '%'||?1||'%' ) ")
    List<PersonFeeVo> queryPersonFee390(String ryxx, String start, String end);

    @Query(value = "select new me.zhengjie.business.domain.vo.PersonFeeVo(" +
            "R1.aac001, " +
            "R1.aac003, " +
            "R1.aac002, " +
            "R1.accrymBegn, " +
            "R1.accrymEnd, " +
            "R1.insutype, " +
            "R1.clctClag)" +
            "FROM PersonFee R1 " +
            "WHERE 1=1 " +
            "and ((R1.insutype='330' and accrym_begn >=?3 and accrym_end >=?4 ) or (R1.insutype='310' and accrym_begn >=?2 and accrym_end >=?2 ) or (R1.insutype='410' and accrym_begn >=?2 and accrym_end >=?2 ))" +
            "and (?1 IS NULL OR ?1 = '' OR R1.aac001 like '%'||?1||'%' OR R1.aac002 like '%'||?1||'%' ) ")
    List<PersonFeeVo> queryPersonFee310(String ryxx, String djrq, String start, String end);

    /**
     * 查询本地费用信息
     * @param jzid /
     * @return /
     */
    @Query(value = "select count(*) zl, " +
            "   to_char(to_date(min(BIS_FYMX_KFRQ),'yyyy-mm-dd'), 'yyyy-mm-dd') ryrq, " +
            "   to_char(to_date(max(BIS_FYMX_KFRQ),'yyyy-mm-dd'), 'yyyy-mm-dd') cyrq, " +
            "   sum(BIS_FYMX_ZJ) zfy," +
            "   sum(case when bis_fymx_sfsc is null then 1 else 0 end) wsc," +
            "   sum(case when bis_fymx_sfsc = '1' then 1 else 0 end) ysc" +
            "     from bis_fymx" +
            "    where BIS_FYMX_JZDJ_ID = ?1 ", nativeQuery = true)
    List<Object> queryLocalFyxx(Long jzid);
}